<?php

namespace catchAdmin\stock\controller;

use catchAdmin\product\model\Product;
use catchAdmin\stock\model\InventoryStatement;
use catchAdmin\stock\model\InventoryStatementSon;
use catcher\base\CatchRequest as Request;
use catcher\CatchResponse;
use catcher\base\CatchController;
use catchAdmin\stock\model\Inventory as InventoryModel;
use catchAdmin\stock\model\Stock;
use think\Exception;
use think\facade\Db;
use catcher\CatchAuth;


class Inventory extends CatchController
{
    
    protected $inventoryModel;
    
    /**
     *
     * @time 2022/03/30 10:53
     * @param InventoryModel $inventoryModel
     * @return mixed
     */
    public function __construct(InventoryModel $inventoryModel)
    {
        $this->inventoryModel = $inventoryModel;
    }
    
    /**
     *
     * @throws \think\db\exception\DbException
     * @return \think\response\Json
     */
    public function index(CatchAuth $auth)
    {
        $user = $auth->user();
        return CatchResponse::paginate($this->inventoryModel->getList($user['tenant_id']));
    }
     /**
     *  盘点明细列表
     * @throws \think\db\exception\DbException
     * @return \think\response\Json
     */
    public function indexSon(CatchAuth $auth,Request $request)
    {
        $params = $request->param();

//        $inventoryModel = $this->inventoryModel->where('inventory_id',$params['inventory_id'])->find();
//        if(0 == $inventoryModel->number){
//            return CatchResponse::fail('盘点数量为0，没有明细表');
//        }

        $son = DB::name('db_inventory_son')->where('inventory_id',$params['inventory_id'])->find();
        if(empty($son)){
           //$res = $this->sonCreate($params);
            return CatchResponse::success([],'盘点数量为0，没有明细');
        }
        $where = [];
        $where[] = ['is.inventory_id','=',$params['inventory_id']];
        $data = DB::name('db_inventory_son')
                    ->alias('is')
                    ->leftJoin('db_stock st','is.stock_id = st.stock_id')
                    ->leftJoin('db_warehouse_config wc','is.config_one = wc.config_id')
                    ->leftJoin('db_inventory i','is.inventory_id = i.inventory_id')
                    ->leftJoin('db_project p','st.project_id = p.project_id')
                    ->where($where)
                    ->field('st.goods_name,i.status,p.project_name,wc.name,is.*')
                    ->order('is.id','desc')
                    ->select()
        ;
                                ;
         return CatchResponse::success($data);
    }
    /**
     *  盘点明细添加
     * @throws catcher\CatchAuth
     * @return array
     */
    public function sonCreate($params)
    {
        $auth = new CatchAuth;
        $user = $auth->user();
        $config_id = DB::name('db_inventory')->where('inventory_id',$params['inventory_id'])->value('config_id');
        $params['config_id'] = $config_id;
        $params = $this->good_count($params);
        $data = [];
       foreach ($params['stock'] as $key=>$value){
           $data[$key]['inventory_id'] = $params['inventory_id'];
           $data[$key]['inventory_son_id'] = date('Ymd').mt_rand();
           $data[$key]['tray_id'] = $value['tray_id'];
           $data[$key]['box_id'] = $value['box_id'];
           $data[$key]['slice_id'] = $value['slice_id'];
           $data[$key]['Good_Qty'] = $value['Good_Qty'];
           $data[$key]['Hold_Qty'] = $value['Hold_Qty'];
           $data[$key]['NG_Qty'] = $value['NG_Qty'];
           $data[$key]['config_one'] = $value['config_id'];
           $data[$key]['packing_id'] = $value['packing_id'];
           $data[$key]['warehousing_id'] = $value['warehousing_id'];
           $data[$key]['warehousing_son_id'] = $value['warehousing_son_id'];
           $data[$key]['tenant_id'] = $user['tenant_id'];
           $data[$key]['created_at'] = time();
           $data[$key]['updated_at'] = time();
           $data[$key]['creator_id'] = $params['creator_id'];
           $data[$key]['stock_id'] = $value['stock_id'];


       }
       $res  = DB::name('db_inventory_son')->insertAll($data);
       if($res){
           return array('status'=>1);
       } else {
           return array('status'=>0);
       }
    }
     /**
     *  盘点明细修改
     * @throws \think\db\exception\DbException
     * @return \think\response\Json
     */
    public function sonUpdate(Request $request)
    {
        $params = $request->param();
        Db::startTrans();
        try {
            unset($params['creator_id']);
            $inventory_id = '';
            //修改盘点明细
            $accuracy = 0;
             foreach ($params as $key => $value){
                  $inventory_id = $value['inventory_id'];
                  $data['number'] = $value['number'];
                  $data['disc_surplus'] = $value['disc_surplus'];
                  $data['disc_deficit'] = $value['disc_deficit'];
                  DB::name('db_inventory_son')->where('id',$value['id'])->update($data);

                  if($data['disc_deficit'] == 0 && $data['disc_surplus'] == 0 ){
                      ++$accuracy;
                  }
             }

            //修改盘存单状态
            $count =  DB::name('db_inventory_son')->field('SUM(disc_surplus) as disc_surplus,SUM(disc_deficit) as disc_deficit,SUM(number) as total_number')->where('inventory_id',$inventory_id)->find();

             //正确率
            $inventory_count = Db::table('db_inventory_son')->where('inventory_id',$inventory_id)->where('deleted_at',0)->count();
            $accuracy_count = Db::table('db_inventory_son')->where([
                ['deleted_at','=',0],
                ['inventory_id','=',$inventory_id],
                ['disc_deficit','=',0],
                ['disc_surplus','=',0]
            ])->count();

            $count['accuracy_rate'] = $inventory_count == 0 ? 0 : number_format($accuracy_count / $inventory_count,2);

            DB::name('db_inventory')->where('inventory_id',$inventory_id)->update($count);
            Db::commit();
            return CatchResponse::success('', '保存成功');
        } catch (\Exception $exception) {
            echo $exception;
             Db::rollback();
             return CatchResponse::fail('保存失败');
        }
    }
    
    /**
     *  盘点主单添加
     * @throws \think\db\exception\DbException
     * @return \think\response\Json
     */
    public function create(CatchAuth $auth,Request $request)
    {
        $params = $request->param();
        $user = $auth->user();
        $tenant_id = $user['tenant_id'];

        if(!array_key_exists('inventory_type',$params)){//前台不穿惨
            $params['inventory_type'] = 0;
        }

        if($tenant_id !== 0){
            $andTenantWhere = ['tenant_id','=',$tenant_id];
        }else{
            $andTenantWhere = [];
        }

        if(0 == $params['inventory_type']){//库位盘点
            // 找出库位下级
            $where = [];
            $where[] = ['config_id','=',$params['config_id']];
            $where[] = ['deleted_at','=',0];
            $config = DB::name('db_warehouse_config')->field('config_id,id')->where($where)->find();
            $data = $this->config_name($config['id']);
            $data['config_id'][] = $params['config_id'];
        }elseif (1 == $params['inventory_type']){//商品盘点
            $stock_where =  [
//                ['tenant_id','=',$user['tenant_id']],
                ['status','<>',"1"],
                ['deleted_at','=',0]
            ];

            $stock_where = array_merge($stock_where,$andTenantWhere);

            if(1 === $params['is_all_product']){//部分
                $stock_where[] = ['goods_id','in',$params['goods_ids']];
                $params['goods_ids'] = implode(',',$params['goods_ids']);
            }else{//全部
                $params['is_all_product'] = 0;
            }

            $stock_config_ids = Db::table('db_stock')->where($stock_where)->column('config_id');
            $data['config_id'] = array_values(array_unique($stock_config_ids));
        }elseif (2 == $params['inventory_type']){//动碰盘点
            $tray_config_ids = Db::table('db_tray')
//                ->where('tenant_id',$user['tenant_id'])
                ->where('deleted_at',0)
                ->where('status','<>','0');

            if(!empty($andTenantWhere)){
                $tray_config_ids = $tray_config_ids->where('tenant_id',$user['tenant_id']);
            }

            $tray_config_ids = $tray_config_ids
                ->whereBetweenTime('created_at', $params['start_datetime'], $params['end_datetime'])
                ->column('config_id');
            $data['config_id'] = array_values(array_unique($tray_config_ids));
        }

        $params['tenant_id'] = $user['tenant_id'];
        $params['auth_id'] = $request->user()->id;

        //找出库存
        $where = [];
        $where[] = ['config_id','in',$data['config_id']];
        $where[] = ['status','<>',"1"];
        $where[] = ['deleted_at','=',0];


        //查询今年 盘点数量
        $this_year_count =  Db::table('db_inventory')
            ->where('tenant_id',$user['tenant_id'])
            ->whereYear('created_at')->count();

        $co_code = Db::table('users')->where('id',$user['tenant_id'])->value('co_code');
        if($co_code){
            $head = strtoupper($co_code) . "CC";
        }else{
            $head = "CIWHCC";//CIWHCC23001
        }
        $params['inventory_id'] = $head  . date("y") . str_pad($this_year_count + 1,5,'0',STR_PAD_LEFT);


        $inventory_son_insert_data = [];

        //如果是部分商品，还要过滤一下
        if(1 == $params['inventory_type'] && 1 === $params['is_all_product']){
            $where[] = ['goods_id','in',explode(',',$params['goods_ids'])];
        }

        $stock_result = Db::table('db_stock')->where($where)->select()->toArray();

        $total_num = 0;
        $total_box_num = 0;
        $box_ids = [];

        //明细单数据
        foreach ($stock_result as $value){
            $one_num = $value['Good_Qty'] + $value['Hold_Qty'] + $value['NG_Qty'];
            $total_num += $one_num;
            //计算箱数
            if(empty($value['box_id']) && empty($value['slice_id'])){//托
                if(0 == $value['packing_num']){//不懂库存表为啥有0包装规格，先过滤了
                    continue;
                }
                $total_box_num += ceil($one_num / $value['packing_num']);
            }else{//箱/件
                $box_ids[] = $value['box_id'];
            }

            //明细
            $son_data  = [];
            $son_data['inventory_id'] = $params['inventory_id'];
            $son_data['inventory_son_id'] = date('Ymd').mt_rand();
            $son_data['tray_id'] = $value['tray_id'];
            $son_data['box_id'] = $value['box_id'];
            $son_data['slice_id'] = $value['slice_id'];
            $son_data['Good_Qty'] = $value['Good_Qty'];
            $son_data['Hold_Qty'] = $value['Hold_Qty'];
            $son_data['NG_Qty'] = $value['NG_Qty'];
            $son_data['config_one'] = $value['config_id'];
            $son_data['packing_id'] = $value['packing_id'];
            $son_data['warehousing_id'] = $value['warehousing_id'];
            $son_data['warehousing_son_id'] = $value['warehousing_son_id'];
            $son_data['tenant_id'] = $user['tenant_id'];
            $son_data['created_at'] = time();
            $son_data['updated_at'] = time();
            $son_data['creator_id'] = $params['creator_id'];
            $son_data['stock_id'] = $value['stock_id'];

            //设置默认盘亏等于数量
            $son_data['disc_deficit'] = $value['Good_Qty'] + $value['Hold_Qty'] + $value['NG_Qty'];

            $inventory_son_insert_data[] = $son_data;
        }

        $total_box_num += count(array_values(array_unique($box_ids)));


        $params['number'] = $total_num;
        $params['box_number'] = $total_box_num;


        Db::startTrans();
        try {
            //主
            $this->inventoryModel->storeBy($params);
            //明细
            DB::name('db_inventory_son')->insertAll($inventory_son_insert_data);

            Db::commit();
            return CatchResponse::success('success');
        }catch (\Exception $exception) {
            Db::rollback();
            throw new Exception('盘点创建失败' . $exception->getMessage());
        }



    }
    /**
     * 完结盘点
     * @time 2022/01/23 13:53
     * @return Response
     */
    public function delete(Request $request)
    {
        $id = $request->param('id');
        $data['status'] = 1;
        return CatchResponse::success($this->inventoryModel->updateBy($id,$data));
    }
    
    /**
     * 统计仓库商品总和
     */
    public function good_count($params){
        // 找出库位下级
        $where = [];
        $where[] = ['config_id','=',$params['config_id']];
        $where[] = ['deleted_at','=',0];
        $config = DB::name('db_warehouse_config')->field('config_id,id')->where($where)->find();
        $data = $this->config_name($config['id']);
        $data['config_id'][] = $params['config_id'];

        //找出库存
        $where = [];
        $where[] = ['s.config_id','in',$data['config_id']];
        $where[] = ['s.status','<>',"1"];

        $stock = DB::name('db_stock')->alias('s')
//                ->join('db_box b','s.tray_id = b.tray_id and b.status <> "1" and s.config_id = b.config_id','LEFT')
//                ->join('db_slice sl','b.box_id = sl.box_id and sl.status = "0" and b.config_id = sl.config_id','LEFT')
//                ->field('s.box_id,s.slice_id,s.stock_id,s.tray_id,s.box_number,s.Good_Qty,s.Hold_Qty,s.NG_Qty,s.config_id,s.warehousing_id,s.warehousing_son_id,s.packing_id,b.box_id,b.Good_Qty as Good_Qty2,b.Hold_Qty as Hold_Qty2,b.NG_Qty as NG_Qty2,b.config_id as config_id2,sl.slice_id,sl.number as Good_Qty3,sl.config_id as config_id3,sl.goods_status')
                ->field('s.*')
                ->where($where)
                ->select()
                ->toArray();
        
        foreach ($stock as $key => $value) {

//            if(!empty($value['tray_id']) && !empty($value['box_id']) && empty($value['slice_id'])){
//                //表示拆托没有拆箱
//                $stock[$key]['Good_Qty'] = $stock[$key]['Good_Qty2'];
//                $stock[$key]['Hold_Qty'] = $stock[$key]['Hold_Qty2'];
//                $stock[$key]['NG_Qty'] = $stock[$key]['NG_Qty2'];
//                $stock[$key]['config_id'] = $stock[$key]['config_id2'];
//
//            }elseif(!empty($value['tray_id']) && !empty($value['box_id']) && !empty($value['slice_id'])){
//                //表示拆托拆箱成片
//                $stock[$key]['Good_Qty'] = $value['goods_status'] == 'Good' ? $stock[$key]['Good_Qty3'] : 0;
//                $stock[$key]['Hold_Qty'] = $value['goods_status'] == 'Hold' ? $stock[$key]['Good_Qty3'] : 0;
//                $stock[$key]['NG_Qty'] = $value['goods_status'] == 'NG' ? $stock[$key]['Good_Qty3'] : 0;
//                $stock[$key]['config_id'] = $stock[$key]['config_id3'];
//
//            }
            unset($stock[$key]['Good_Qty2']);
            unset($stock[$key]['Hold_Qty2']);
            unset($stock[$key]['NG_Qty2']);
            unset($stock[$key]['Good_Qty3']);
            unset($stock[$key]['config_id2']);
            unset($stock[$key]['config_id3']);
            unset($stock[$key]['goods_status']);
            
            // foreach ($config as $k1 => $v1) {
            //     if($stock[$key]['config_id'] == $v1['config_id']){
            //         $data = $this->config_name($v1['id']);
            //
            //         // $stock[$key]['config_name_one'] = $data['name'][0];
            //         // $stock[$key]['config_name_two'] = $data['name'][1];
            //         // $stock[$key]['config_name_three'] = $data['name'][2];
            //         $stock[$key]['config_one'] = $data['config_id'][0];
            //         $stock[$key]['config_two'] = $data['config_id'][1];
            //         $stock[$key]['config_three'] = $data['config_id'][2];
            //     }
            // }
            //
            // if($stock[$key]['config_one'] != $params['config_id'] && $stock[$key]['config_two'] != $params['config_id'] && $stock[$key]['config_three'] != $params['config_id']){
            //     unset($stock[$key]);
            // }
            // unset($stock[$key]['config_id']);
        }

        $stock = array_values($stock);
        
        $params['stock'] = $stock;
        $params['number'] = array_sum(array_column($stock,'Good_Qty')) + array_sum(array_column($stock,'Hold_Qty')) + array_sum(array_column($stock,'NG_Qty'));

        $box_number = DB::name('db_stock')->field('SUM(box_number) as box_number')->where('tray_id','in',array_unique(array_column($stock,'tray_id')))->find();
        $params['box_number'] = $box_number['box_number'];
        return $params;
    }
    /**
	 * 父类ID查找子分类
	**/
	public function config_name($config_id, $array = []){
    	$info = DB::name('db_warehouse_config')->where('parent_id',$config_id)->field('id,name,config_id')->select()->toArray();
	  	if($info){
	        foreach($info as $key => $val){
	            $array['id'][] = $val['id'];
	            $array['name'][] = $val['name'];
                $array['config_id'][] = $val['config_id'];
	            $array = $this->config_name($val['id'],$array);
	        }
	    }
	    return $array;
	}

    public function statementCreate(CatchAuth $auth,Request $request){
        $params = $request->param();

        $goods_ids = $params['goods_ids'];

        $user = $auth->user();
        $params['statement_datetime'] = date("Y-m-d H:i:s");
        $params['tenant_id'] = $user['tenant_id'];

        if(true ===  $params['is_all_product']){//全部商品
            $where=[];
//            $where[]=['deleted_at','=',0];
            if($user['tenant_id']!=0){
                $where[]=['tenant_id','=',$user['tenant_id']];
            }
//            $list=Db::table('db_product')
            $list= (new Product)
                ->field('goods_id,goods_name,packing_id,serial_number')->where($where)
                ->order('id','desc')
                ->select()->toArray();

            $goods_ids = array_column($list,'goods_id');
            $params['is_all_product'] = 0;
        }else{
            $params['is_all_product'] = 1;
        }

        $params['goods_ids'] = implode(',',array_unique($goods_ids));

        $inventoryStatementModel = new InventoryStatement;

        $all_last_month_number = $all_this_month_in_number = $all_this_month_out_number = $all_current_number =  0;

        $all_current_Good_Qty = $all_current_Hold_Qty = $all_current_NG_Qty = 0;

        Db::startTrans();
        try {
            //主
            $pid = $inventoryStatementModel->storeBy($params);

            $statementSonInsertData = [];

            $goods_ids = array_unique($goods_ids);

            //查询本月所有入库订单数量
            $this_month_in_number_arr =  Db::table('db_warehousing_son')
                ->field('id,goods_id,actual_number')
                ->whereMonth('created_at')
                ->where('goods_id','in',$goods_ids)->where('deleted_at',0)->where('status','<>',0)
                ->select();

            $this_month_in_number_arr_temp = [];
            foreach ($this_month_in_number_arr as $value){
                $this_month_in_number_arr_temp['G' . $value['goods_id']][] = $value['actual_number'];
            }

            //查询本月所有出库订单数量
            $this_month_out_number_arr =  Db::table('db_warehouse_out_son')
                ->field('id,goods_id,out_number')
                ->whereMonth('created_at')
                ->where('goods_id','in',$goods_ids)->where('deleted_at',0)->where('status','<>',0)
                ->select();

            $this_month_out_number_arr_temp = [];
            foreach ($this_month_out_number_arr as $value){
                $this_month_out_number_arr_temp['G' . $value['goods_id']][] = $value['out_number'];
            }

            //查询商品库存数量
            $stock_arr = (Db::table('db_stock')
//                    ->field('sum(Good_Qty + Hold_Qty + NG_Qty) as stock')
                ->field('(Good_Qty + Hold_Qty + NG_Qty) as stock,
                    Good_Qty as Good_Qty_stock,
                    Hold_Qty as Hold_Qty_stock,
                    NG_Qty as NG_Qty_stock,
                    goods_id
                    '
                )
                ->where('goods_id','in',$goods_ids)->where('deleted_at',0)->where('status','<>','1')
                ->select());

            $stock_arr_temp = [];
            foreach ($stock_arr as $value){
                $stock_arr_temp['G' . $value['goods_id']]['stock'][] = $value['stock'];
                $stock_arr_temp['G' . $value['goods_id']]['Good_Qty_stock'][] = $value['Good_Qty_stock'];
                $stock_arr_temp['G' . $value['goods_id']]['Hold_Qty_stock'][] = $value['Hold_Qty_stock'];
                $stock_arr_temp['G' . $value['goods_id']]['NG_Qty_stock'][] = $value['NG_Qty_stock'];
            }

           // dd($stock_arr_temp);

            foreach ($goods_ids as $value){
                $goods_result = (new Product)->where('goods_id',$value)->find();
                $goods_name = $goods_result['goods_name'];
                $goods_Cname = $goods_result['goods_Cname'];

                //单个商品本月入库数量 whereMonth
                $this_month_in_number =  array_key_exists("G" . $value,$this_month_in_number_arr_temp)
                    ? array_sum($this_month_in_number_arr_temp["G" . $value])
                    : 0;

                //单个商品本月出库数量
                $this_month_out_number =  array_key_exists("G" . $value,$this_month_out_number_arr_temp)
                    ? array_sum($this_month_out_number_arr_temp["G" . $value])
                    : 0;

                //单个商品当前库存
                if(array_key_exists("G" . $value,$stock_arr_temp)){
                    $current_number = (int)array_sum($stock_arr_temp["G" . $value]['stock']);
                    $current_Good_Qty = (int)array_sum($stock_arr_temp["G" . $value]['Good_Qty_stock']);
                    $current_Hold_Qty = (int)array_sum($stock_arr_temp["G" . $value]['Hold_Qty_stock']);
                    $current_NG_Qty = (int)array_sum($stock_arr_temp["G" . $value]['NG_Qty_stock']);
                }else{
                    $current_number = 0;
                    $current_Good_Qty = 0;
                    $current_Hold_Qty = 0;
                    $current_NG_Qty = 0;
                }

                //上月结余 等于 当前库存 + 本月出库 - 本月入库
                $last_month_number = $current_number + $this_month_out_number - $this_month_in_number;

                $statementSonData = [];
                $statementSonData['statement_id'] = $pid;
                $statementSonData['goods_name'] = $goods_name;
                $statementSonData['goods_Cname'] = $goods_Cname;
                $statementSonData['goods_id'] = $value;
                $statementSonData['last_month_number'] = $last_month_number;
                $statementSonData['this_month_in_number'] = $this_month_in_number;
                $statementSonData['this_month_out_number'] = $this_month_out_number;

                $statementSonData['current_number'] = $current_number;
                $statementSonData['current_Good_Qty'] = $current_Good_Qty;
                $statementSonData['current_Hold_Qty'] = $current_Hold_Qty;
                $statementSonData['current_NG_Qty'] = $current_NG_Qty;


                $statementSonData['tenant_id'] = $user['tenant_id'];
                $statementSonData['creator_id'] = $user->id;

                $statementSonInsertData[] = $statementSonData;


                $all_last_month_number += $last_month_number;
                $all_this_month_in_number += $this_month_in_number;
                $all_this_month_out_number += $this_month_out_number;

                $all_current_number += $current_number;
                $all_current_Good_Qty += $current_Good_Qty;
                $all_current_Hold_Qty += $current_Hold_Qty;
                $all_current_NG_Qty += $current_NG_Qty;
            }

            //明细数据
            $inventoryStatementSonModel = new InventoryStatementSon;

            $inventoryStatementSonModel->insertAllBy($statementSonInsertData);

            //修改主表数据
            $updateData = [
                'last_month_number'=>$all_last_month_number,
                'this_month_in_number'=>$all_this_month_in_number,
                'this_month_out_number'=>$all_this_month_out_number,
                'current_number'=>$all_current_number,
                'current_Good_Qty'=>$all_current_Good_Qty,
                'current_Hold_Qty'=>$all_current_Hold_Qty,
                'current_NG_Qty'=>$all_current_NG_Qty,
            ];

            $inventoryStatementModel->updateBy($pid,$updateData);

            Db::commit();
            return CatchResponse::success();
        } catch (\Exception $exception) {
            Db::rollback();
            return CatchResponse::fail('保存失败' . $exception->getMessage());
        }
    }

    public function statement(CatchAuth $auth,Request $request){
        $user = $auth->user();

        $inventoryStatementModel = new InventoryStatement;
        return CatchResponse::paginate($inventoryStatementModel->getList($user['tenant_id']));
    }

    //盘点，商品库存明细
    public function commodityInventory($id,CatchAuth $auth,Request $request){
        $user = $auth->user();
        $inventoryStatementSonModel = new InventoryStatementSon;

        return CatchResponse::success($inventoryStatementSonModel->getNoPageList($id,$user['tenant_id']));
    }

    /**
     * 导出盘点单
     * @param Request $request
     * @param Excel $excel
     * @param inventoryStatementExport $export
     * @return \think\response\Json
     */
    public function exportStatement(Request $request, \catcher\library\excel\Excel $excel, \catchAdmin\stock\excl\InventoryStatementExport $export)
    {
        return CatchResponse::success($excel->save($export, 'export/inventory_statement'));
    }
}